package jude.java.money.batch.task;

import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import jude.java.money.batch.bean.Domain;
import jude.java.money.batch.bean.DomainAgent;
import jude.java.money.batch.bean.Site;
import jude.java.money.batch.bean.Space;
import jude.java.money.batch.memory.Domains;
import jude.java.money.batch.memory.Emails;
import jude.java.money.batch.memory.IP;
import jude.java.money.batch.memory.Sites;
import jude.java.money.batch.memory.Spaces;
import jude.java.money.tools.ExcelExporter;
import jude.java.util.Utils;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.CellStyle;

/*
 * HSSFColor.COLOR.index
* 颜色对照表 数字代表颜色索引
    8： BLACK
    60： BROWN
    59： OLIVE_GREEN
    58： DARK_GREEN
    56： DARK_TEAL
    18： DARK_BLUE 32
    62： INDIGO
    63： GREY_80_PERCENT
    53： ORANGE
    19： DARK_YELLOW
    17： GREEN
    21： TEAL 38
    12： BLUE 39
    54： BLUE_GREY
    23： GREY_50_PERCENT
    10： RED
    52： LIGHT_ORANGE
    50： LIME
    57： SEA_GREEN
    49： AQUA
    48： LIGHT_BLUE
    20： VIOLET
    36： VIOLET
    55： GREY_40_PERCENT
    14： PINK 33
    51： GOLD
    13： YELLOW 34
    11： BRIGHT_GREEN 35
    35： BRIGHT_GREEN
    15： TURQUOISE 35
    16： DARK_RED 37
    40： SKY_BLUE
    25： PLUM 61
    22： GREY_25_PERCENT
    45： ROSE
    43： LIGHT_YELLOW
    42： LIGHT_GREEN
    27：LIGHT_TURQUOISE 41
    44： PALE_BLUE
    46： LAVENDER
    9： WHITE
    24： CORNFLOWER_BLUE
    26： LEMON_CHIFFON
    25： MAROON
    28： ORCHID
    29： CORAL
    30： ROYAL_BLUE
    31： LIGHT_CORNFLOWER_BLUE
*/
/** 导出空间域名 */
public class SpaceDomainExporter {
	private static final String SPACE_SHEET_NAME = "空间商";
	private static final String DOMAIN_SHEET_NAME = "域名商";
	private static final String EMAIL_SHEET_NAME = "EMail";
	
	private ExcelExporter exporter;
	private CellStyle titleStyle;
	private CellStyle subtitleStyle;
	private CellStyle style1;
	private CellStyle style2;
	private CellStyle ignoredStyle;
	private CellStyle highligntStyle;

	public SpaceDomainExporter(String fileName) {
		exporter = new ExcelExporter(fileName, "信息");
		HSSFFont font = exporter.getWorkbook().createFont();
		font.setFontHeightInPoints((short) 14); 		// 字体高度
//		font.setColor(HSSFFont.COLOR_RED); 				// 字体颜色
		font.setFontName("黑体"); 						// 字体
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 	// 宽度
//		font.setItalic( true ); 						// 是否使用斜体
		
		titleStyle = getDefaultStyle();
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setWrapText(false);
		titleStyle.setFont(font);
		
		font = exporter.getWorkbook().createFont();
		font.setFontHeightInPoints((short) 12); 		// 字体高度
		font.setColor((short)12); 						// 字体颜色
//		font.setFontName("黑体"); 						// 字体
//		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 	// 宽度
//		font.setItalic(true); 							// 是否使用斜体
		subtitleStyle = getDefaultStyle();
		subtitleStyle.setLocked(true);
		subtitleStyle.setHidden(true);
		subtitleStyle.setFont(font);
		
		font = exporter.getWorkbook().createFont();
		font.setFontHeightInPoints((short) 9); 			// 字体高度
		font.setColor((short)28);
		font.setItalic(true);
		style1 = getDefaultStyle();
		style1.setFont(font);
		
		font = exporter.getWorkbook().createFont();
		font.setFontHeightInPoints((short) 9);	
		font.setColor((short)54);
		style2 = getDefaultStyle();
		style2.setFont(font);
		
		font = exporter.getWorkbook().createFont();
//		font.setFontHeightInPoints((short) 9);
		font.setColor((short)10);
		highligntStyle = getDefaultStyle();
		highligntStyle.setFont(font);
		
		font = exporter.getWorkbook().createFont();
//		font.setFontHeightInPoints((short) 9);	
		font.setColor((short)22);
		ignoredStyle = getDefaultStyle();
		ignoredStyle.setFont(font);
		
	}
	
	private CellStyle getDefaultStyle() {
		CellStyle defaultStyle = exporter.getWorkbook().createCellStyle();
		defaultStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		defaultStyle.setLocked(false);
		return defaultStyle;
	}
	
	public void export() {
		exporter.setDefaultStyle(getDefaultStyle());
		exportInfos();
		exportSpaceAgents();
		exportDomainAgents();
		exportSites();
		exportEmails();
		exporter.exportXLS();
	}
	
	private void exportInfos() {
		exporter.createRow(0);
		exporter.createRow();
		exporter.merge(0, 2);
		exporter.setCell(0, "空间", titleStyle);
		
		exporter.createRow();
		int n = 0;
		exporter.setCell(n++, "ix");
		exporter.setCell(n++, "manage.ixwebhosting.com");
		exporter.setCell(n++, "不大稳定, 但提供16个IP和3个域名, 后台管理麻烦");

		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "BlueHost");
		exporter.setCell(n++, "www.bluehost.com");
		exporter.setCell(n++, "限制20万文件和2G MySql或1000个表");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "UnderHost");
		exporter.setCell(n++, "underhost.com");
		exporter.setCell(n++, "");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "Santrex");
		exporter.setCell(n++, "www.santrex.net");
		exporter.setCell(n++, "");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "Altus");
		exporter.setCell(n++, "altushost.com");
		exporter.setCell(n++, "");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "领航");
		exporter.setCell(n++, "");
		exporter.setCell(n++, "仅支持一个域名; 不安全,老是被人黑");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "百惠通");
		exporter.setCell(n++, "www.idc23.com");
		exporter.setCell(n++, "硬盘空间才200M, 后台为DirectAdmin, 管理不大方便");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "SugarHosts");
		exporter.setCell(n++, "");
		exporter.setCell(n++, "");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "YahooHost亚狐");
		exporter.setCell(n++, "www.yahoohost.cn");
		exporter.setCell(n++, "离岸的485/年");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "HostOurs");
		exporter.setCell(n++, "www.hostours.com");
		exporter.setCell(n++, "68元年");

		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "天成世纪");
		exporter.setCell(n++, "www.tcbest.com");
		exporter.setCell(n++, "面板Virtualmin/Webmin, 可跨目录, 面板/数据库/FTP 用户名一样, 空间小");

		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "帝乐");
		exporter.setCell(n++, "fangpaihosting.com");
		exporter.setCell(n++, "");
		
		exporter.createRow();
		exporter.createRow();
		exporter.merge(0, 2);
		exporter.setCell(0, "域名", titleStyle);
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "紫田");
		exporter.setCell(n++, "zitian.cn");
		exporter.setCell(n++, "");

		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "奕盛");
		exporter.setCell(n++, "eaysun.com");
		exporter.setCell(n++, "");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "万维景城");
		exporter.setCell(n++, "72e.wanweiwang.cn");
		exporter.setCell(n++, "");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "零度");
		exporter.setCell(n++, "www.0do.net");
		exporter.setCell(n++, "0do");

		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "netfirms");
		exporter.setCell(n++, "www.netfirms.com");
		exporter.setCell(n++, "");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "Godaddy");
		exporter.setCell(n++, "www.godaddy.com");
		exporter.setCell(n++, "");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "cm.cm");
		exporter.setCell(n++, "www.cm.cm");
		exporter.setCell(n++, "");
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "");
		exporter.setCell(n++, "");
		exporter.setCell(n++, "");
		
		exporter.setColumnWidth(150, 250, 400);
/*		
		exporter.createRow();
		exporter.createRow();
		exporter.createRow();
		exporter.merge(0, 2);
		exporter.setCell(0, "在 " + SPACE_SHEET_NAME + " 和 " + DOMAIN_SHEET_NAME + " 页进行如下操作", subtitleStyle);
		exporter.createRow();
		exporter.merge(5, 0, 2);
		exporter.setCell(0, "1, CTRL+G 调出定位, 按'定位条件'按钮\r\n" +
				"2, 选择'常量', 并把'公式'下面的'数字'选中, 其它的勾去掉, 并按确定\r\n" +
				"3, 菜单: 格式/条件格式\r\n" +
				"4, 选择/填入: 单元格数值,介于,=TODAY()-10,=TODAY()+10\r\n" +
				"5, 格式中选择 红色等醒目颜色, 按确定");
		exporter.createRow();
		exporter.createRow();
		exporter.createRow();
		exporter.createRow();
		exporter.createRow();
		exporter.merge(0, 2);
		exporter.setCell(0, "菜单: 工具/选项/安全性, 设置文件密码", subtitleStyle);
*/
	}
	
	private void exportEmails() {
		exporter.createSheet(EMAIL_SHEET_NAME);
		exporter.createRow(0);
		int n = 0;
		exporter.setCell(n++, "#", titleStyle, 50);
		exporter.setCell(n++, "邮箱", titleStyle, 250);
		exporter.setCell(n++, "*", titleStyle, 200);
		
		int c = 1;
		Map<String, String> emails = Emails.get();
		for (Map.Entry<String, String> e : emails.entrySet()) {
			exporter.createRow();
			n = 0;
			exporter.setCell(n++, String.valueOf(c++));
			exporter.setCell(n++, e.getKey());
			exporter.setCell(n++, e.getValue());
		}
		exporter.freeze(1, 0);
	}
	
	private void exportDomainAgents() {
		exporter.createSheet(DOMAIN_SHEET_NAME);
		exporter.createRow(0);
		int n = 0;
		exporter.setCell(n++, "", titleStyle, 50);
		exporter.setCell(n++, "域名", titleStyle, 200);
		exporter.setCell(n++, "到期日期", titleStyle, 100);
		exporter.setCell(n++, "注册商", titleStyle, 100);
		exporter.setCell(n++, "密码", titleStyle, 100);
		exporter.setCell(n++, "备注", titleStyle, 300);
		
		List<DomainAgent> agents = Domains.getAgents();
		for (DomainAgent agent : agents) {
			exporter.createRow();
			n = 0;
			exporter.setCell(n++, "", subtitleStyle);
			exporter.setCell(n++, agent.getName(), subtitleStyle);
			exporter.setCell(n++, agent.getUser(), subtitleStyle);
			exporter.setCell(n++, agent.getPass(), subtitleStyle);
			exporter.setCell(n++, agent.getUrl(), subtitleStyle);
			List<Domain> domains = agent.getDomains();
			if (domains == null)
				continue;
			int k = 0;
			for (Domain domain : domains) {
				exporter.createRow();
				n = 0;
//				Date d = domain.getExpireDate();
//				CellStyle style = d != null && d.getTime() < System.currentTimeMillis()-1*3600*24*1000 ? ignoredStyle : null;
				CellStyle style = domain.isExpired() ? ignoredStyle : null;
				exporter.setCell(n++, String.valueOf(++k), style);
				exporter.setCell(n++, domain.getName(), style);
				exporter.setCell(n++, domain.getExpireDate(), style);
				exporter.setCell(n++, domain.getProvider(), style);
				exporter.setCell(n++, domain.getPass(), style);
				exporter.setCell(n++, domain.getRemark(), style);
			}
			exporter.createRow();
		}
		exporter.applyDateConditionFormatting();
		exporter.freeze(1, 0);
	}
	
	private void exportSites() {
		exporter.createSheet("网站");
		
		exporter.createRow(0);
		int n = 0;
		exporter.setCell(n++, "空间", titleStyle, 100);
		exporter.setCell(n++, "地址", titleStyle, 120);
		exporter.setCell(n++, "用户名", titleStyle, 90);
		exporter.setCell(n++, "密码", titleStyle, 90);
		exporter.setCell(n++, "到期日期", titleStyle, 90);
		exporter.setCell(n++, "限制", titleStyle, 90);
		
		exporter.createRow();
		n = 0;
		exporter.setCell(n++, "", titleStyle);
		exporter.setCell(n++, "域名", titleStyle);
		exporter.setCell(n++, "51.la", titleStyle);
		exporter.setCell(n++, "RP", titleStyle);
		exporter.setCell(n++, "Template", titleStyle);
		exporter.setCell(n++, "News", titleStyle);
		
//		exporter.setCell(n++, "G Verification", titleStyle, 90);
		exporter.setCell(n++, "GMAIL", titleStyle, 90);
		exporter.setCell(n++, "IP", titleStyle, 90);
		exporter.setCell(n++, "Backend", titleStyle, 90);
		exporter.setCell(n++, "备注", titleStyle, 300);
		List<Space> spaces = Spaces.getSpaces();
		for (Space space : spaces) {
			List<Site> sites = Sites.listSites(Site.KEY_SPACE, space.getName());
			String[] domains = space.getDomains();
			String[] parked = space.getParked();
			String[] blogs = space.getBlogs();
			Date d = space.getEndDate();
			if ((d == null || d.getTime() < System.currentTimeMillis()) && Utils.isEmpty(sites) && Utils.isEmpty(domains) && Utils.isEmpty(blogs) && Utils.isEmpty(parked))
				continue;
			Set<String> parked2 = new LinkedHashSet<String>();
			if (parked != null)
				for (int i = 0; i < parked.length; i++) {
					parked2.add(parked[i]);
				}
			if (space.getEndDate() == null || space.getEndDate().getTime() < System.currentTimeMillis() - 24*3600*1000) {
				if (sites != null) {
					for (Site site : sites) {
						parked2.add(site.getDomain());
					}
					sites.clear();
				}
				if (domains != null) {
					for (int i = 0; i < domains.length; i++) {
						parked2.add(domains[i]);
					}
					domains = null;
				}
			}
			exporter.createRow();
			n = 0;
			int index = space.getIndex();
			exporter.setHyperlink(n++, SPACE_SHEET_NAME+"!A"+(2+index), subtitleStyle);
			exporter.setFormula(n++, "OFFSET("+SPACE_SHEET_NAME+"!A2, "+index+", COLUMN()-1)", subtitleStyle);
			exporter.setFormula(n++, "OFFSET("+SPACE_SHEET_NAME+"!A2, "+index+", COLUMN()-1)", subtitleStyle);
			exporter.setFormula(n++, "OFFSET("+SPACE_SHEET_NAME+"!A2, "+index+", COLUMN()-1)", subtitleStyle);
			exporter.setFormula(n++, "IF(OFFSET("+SPACE_SHEET_NAME+"!A2, "+index+", COLUMN())=0,\"\",OFFSET("+SPACE_SHEET_NAME+"!A2, "+index+", COLUMN()))", exporter.getDateStyle(subtitleStyle), true);	//end date
			exporter.setFormula(n++, "IF(OFFSET("+SPACE_SHEET_NAME+"!A2, "+index+", COLUMN()-2)=0,\"\",OFFSET("+SPACE_SHEET_NAME+"!A2, "+index+", COLUMN()-2))", subtitleStyle);							//limit
			List<String> es = new ArrayList<String>();
			for (Site site : sites) {
				exporter.createRow();
				n = 0;
				CellStyle style = Sites.isAbandon(site.getDomain()) ? ignoredStyle : null;
				if (style == null && Domains.isExpired(site.getDomain()))
					style = ignoredStyle;
				exporter.setCell(n++, "");
				exporter.setCell(n++, site.getDomain(), style);
				exporter.setCell(n++, site.get(Site.KEY_51LA), style);
				exporter.setCell(n++, site.get(Site.KEY_RPPAY), style);
				exporter.setCell(n++, site.get(Site.KEY_TEMPLATE), style);
				exporter.setCell(n++, site.get(Site.KEY_NEWS), style);
//				exporter.setCell(n++, site.get(Site.KEY_GV_CODE), style);
				exporter.setCell(n++, site.get(Site.KEY_GMAIL), style);
				exporter.setCell(n++, IP.get(site.getDomain()), style);
				exporter.setCell(n++, site.get(Site.KEY_BACKEND), style);
				exporter.setCell(n++, site.get(Site.KEY_REMARK), style);
				es.add(site.getDomain());
			}
			if (domains != null)
				for (int i = 0; i < domains.length; i++) {
					if (es.contains(domains[i]))
						continue;
					exporter.createRow();
					n = 0;
					CellStyle style = Sites.isAbandon(domains[i]) ? ignoredStyle : null;
					exporter.setCell(n++, "");
					exporter.setCell(n++, domains[i], style);
					Site site = Sites.getSite(domains[i]);
					if (site != null) {
						exporter.setCell(n++, site.get(Site.KEY_51LA), style);
						exporter.setCell(n++, site.get(Site.KEY_RPPAY), style);
						exporter.setCell(n++, site.get(Site.KEY_TEMPLATE), style);
						exporter.setCell(n++, site.get(Site.KEY_NEWS), style);
//						exporter.setCell(n++, site.get(Site.KEY_GV_CODE), style);
						exporter.setCell(n++, site.get(Site.KEY_GMAIL), style);
						exporter.setCell(n++, IP.get(site.getDomain()), style);
						exporter.setCell(n++, site.get(Site.KEY_BACKEND), style);
						exporter.setCell(n++, site.get(Site.KEY_REMARK), style);
					}
				}
			if (!Utils.isEmpty(sites) || !Utils.isEmpty(domains))
				exporter.createRow();
			
			if (!Utils.isEmpty(parked2)) {
				int i = 0;
				for (String domain : parked2) {
					if (i % 6 == 0) {
						n = 0;
						exporter.createRow();
						exporter.setCell(n++, "");
					}
					exporter.setCell(n++, domain, style1);
					i++;
				}
//				for (i = 0; i < parked.length; i++) {
//					if (i % 6 == 0) {
//						n = 0;
//						exporter.createRow();
//						exporter.setCell(n++, "");
//					}
//					exporter.setCell(n++, parked[i], style1);
//				}
				exporter.createRow();
			}
			if (!Utils.isEmpty(blogs)) {
				if (!Utils.isEmpty(parked))
					exporter.createRow();
				for (int i = 0; i < blogs.length; i++) {
					if (i % 6 == 0) {
						n = 0;
						exporter.createRow();
						exporter.setCell(n++, "");
					}
					exporter.setCell(n++, blogs[i], style2);
				}
				exporter.createRow();
			}
		}
		
		exporter.createRow();
		exporter.createRow();
		exporter.createRow();
		exporter.merge(2, 6);
		exporter.setCell(2, "表示已经放弃的/回收的域名", ignoredStyle);
		exporter.createRow();
		exporter.merge(2, 6);
		exporter.setCell(2, "域名曾经依靠在这个空间", style1);
		exporter.createRow();
		exporter.merge(2, 6);
		exporter.setCell(2, "表示该空间上的博客或其它", style2);
		
		exporter.applyDateConditionFormatting();
		exporter.freeze(2, 2);
		exporter.protectSheet("ues");
	}

	private void exportSpaceAgents() {
		exporter.createSheet(SPACE_SHEET_NAME);
		exporter.createRow(0);
		int index = 0;
		exporter.setCell(index++, "名称", titleStyle, 90);
		exporter.setCell(index++, "IP/登录地址", titleStyle, 130);
		exporter.setCell(index++, "用户名", titleStyle, 90);
		exporter.setCell(index++, "密码", titleStyle, 90);
		exporter.setCell(index++, "限制", titleStyle, 110);
		exporter.setCell(index++, "到期日期", titleStyle, 90);
		exporter.setCell(index++, "DNS1", titleStyle, 80);
		exporter.setCell(index++, "DNS2", titleStyle, 80);
		exporter.setCell(index++, "邮箱", titleStyle, 90);
		exporter.setCell(index++, "密码", titleStyle, 90);
		exporter.setCell(index++, "数据库", titleStyle, 90);
		exporter.setCell(index++, "密码", titleStyle, 90);
//		exporter.setCell(index++, "开通时间", titleStyle, 100);
		exporter.setCell(index++, "备注", titleStyle, 300);

		List<Space> spaces = Spaces.getSpaces();
		for (Space space : spaces) {
			exporter.createRow(-1);
			index = 0;
			Date d = space.getEndDate();
			long now = System.currentTimeMillis();
			CellStyle style = null;
			if (d != null) {
				if (Math.abs(now - d.getTime()) < 10 * 24 * 3600)
					style = highligntStyle;
				else if (d.getTime() < now)
					style = ignoredStyle;
			}
			exporter.setCell(index++, space.getName(), style);
			exporter.setCell(index++, space.getUrl(), style);
			exporter.setCell(index++, space.getUser(), style);
			exporter.setCell(index++, space.getPass(), style);
			exporter.setCell(index++, space.getLimit(), style);
			exporter.setCell(index++, space.getEndDate(), style);
			exporter.setCell(index++, space.getDns1(), style);
			exporter.setCell(index++, space.getDns2(), style);
			exporter.setCell(index++, space.getEmail(), style);
			exporter.setCell(index++, space.getApass(), style);
			exporter.setCell(index++, space.getDbuser(), style);
			exporter.setCell(index++, space.getDbpass(), style);
//			exporter.setCell(index++, space.getStartDate(), style);
			exporter.setCell(index++, space.getRemark(), style);
			exporter.setComment(0, space.getRemark());
		}
		exporter.applyDateConditionFormatting();
		exporter.freeze(1, 1);
	}
}
